import os
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# 设置参数
n = 4  # 将区域划分为 n x n 的小网格

# 设置 CSV 文件夹路径和输出路径
folder_path = "D:\\mydata\\生态项目相关\\数据集\\好快\\landset5\\Normalization rsei folder"
output_folder = "D:\\mydata\\生态项目相关\\数据集\\好快\\landset5"
output_file = os.path.join(output_folder, "summary_statistics_16.csv")
filled_output_file = os.path.join(output_folder, "summary_statistics_filled_nan_16.csv")

summary_data = {}
region_ids = []

# 遍历所有文件，记录经纬度范围
all_coords = []

for filename in os.listdir(folder_path):
    if filename.endswith('.csv'):
        file_path = os.path.join(folder_path, filename)
        try:
            df = pd.read_csv(file_path)
            all_coords.append(df[['longitude', 'latitude']])
        except:
            pass

# 获取总体经纬度范围
all_coords_df = pd.concat(all_coords)
min_lon, max_lon = all_coords_df['longitude'].min(), all_coords_df['longitude'].max()
min_lat, max_lat = all_coords_df['latitude'].min(), all_coords_df['latitude'].max()

# 计算每个网格的宽度和高度
lon_step = (max_lon - min_lon) / n
lat_step = (max_lat - min_lat) / n

# 生成网格编号
region_ids = [f'region_{i}' for i in range(n * n)]

# 遍历所有 CSV，统计每个区域的 RSEI 均值
for filename in os.listdir(folder_path):
    if filename.endswith('.csv'):
        file_path = os.path.join(folder_path, filename)

        try:
            # 提取日期
            date_str = filename.split('_')[1]
            date_formatted = datetime.strptime(date_str, '%Y%m%d').strftime('%Y-%m-%d')
            if date_formatted == "2013-04-06":
                date_formatted = "2013-04-04"

            df = pd.read_csv(file_path)

            # 添加列标识每一行属于哪个区域
            df['col'] = ((df['longitude'] - min_lon) // lon_step).astype(int)
            df['row'] = ((df['latitude'] - min_lat) // lat_step).astype(int)

            # 限制在范围内
            df['col'] = df['col'].clip(0, n-1)
            df['row'] = df['row'].clip(0, n-1)

            # 计算 region_id
            df['region_id'] = df['row'] * n + df['col']

            # 分组统计 RSEI 均值
            rsei_means = df.groupby('region_id')['RSEI'].mean()

            # 构建完整行（所有 region 都要有）
            row_data = {f'region_{i}': rsei_means.get(i, np.nan) for i in range(n * n)}
            row_data['date'] = date_formatted
            summary_data[date_formatted] = row_data

        except Exception as e:
            print(f"{filename} 处理失败：{e}")

# 构建最终 DataFrame
result_df = pd.DataFrame.from_dict(summary_data, orient='index')
result_df = result_df.sort_index()  # 按日期排序
result_df.reset_index(drop=True, inplace=True)

# 将 date 列移到第一列
cols = ['date'] + [col for col in result_df.columns if col != 'date']
result_df = result_df[cols]

# 保存结果
result_df.to_csv(output_file, index=False)
print(f"统计结果已保存到: {output_file}")

# 将 'date' 列转换为 datetime 类型
result_df['date'] = pd.to_datetime(result_df['date'])

# 设置起止时间
start_date = result_df['date'].min()
end_date = result_df['date'].max()

# 生成完整的16天间隔的时间序列
full_dates = pd.date_range(start=start_date, end=end_date, freq='16D')

# 以日期为索引重新设置 DataFrame
result_df.set_index('date', inplace=True)

# 重新索引，补全缺失日期，缺失值为 NaN
result_df = result_df.reindex(full_dates)

# 重置索引为列，并改名为 'date'
result_df.reset_index(inplace=True)
result_df.rename(columns={'index': 'date'}, inplace=True)

# 保存补全后的结果
result_df.to_csv(filled_output_file, index=False)
print(f"补全缺失日期后的结果已保存到: {filled_output_file}")

